package com.hxwm.entity;

import com.hxwm.util.Util;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MapperExcel {
    private List<MapperSheet> mapperSheetList;
    private String mapperFilePath;

    public MapperExcel(String mapperFilePath) throws Exception{
        this.mapperFilePath = mapperFilePath;
        this.mapperSheetList = analysisExcelMapper(mapperFilePath);
    }

    /**
     * Excel解析规则：
     * 解析表名：存在多个旧表对应一张新表的可能，表名都在第一行，每三列为一张表，直到读到空单元格，最后一个表名为新表名
     * 解析表字段：
     *      表字段从第四行开始，文件第二行、第三行为文件标识，不需要读取；
     *      表字段从第一列开始，每三列读取一次，直到列结束
     *      当读取到某行的第一列为“end”时，结束文件读取
     * 映射解析：
     *      旧表字段全部没有对应的新表字段（新表字段没有与之对应的旧表字段）
     * 按列循环，构建每一个table(行循环)
     * @return
     * @throws IOException
     */
    public List<MapperSheet> analysisExcelMapper(String filePath) throws Exception {
        //读取excel映射文件
        FileInputStream excelFile = new FileInputStream(new File(filePath));
        Workbook workbook = new XSSFWorkbook(excelFile);
        List<MapperSheet> mapperSheetList = new ArrayList<>();
        int numberOfSheets = workbook.getNumberOfSheets();//获取sheet的个数
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取工作表
            Row firstRow = sheet.getRow(0);//获取第一行
            if(firstRow==null){
                break;
            }
            int colNum = getExcelColNum(firstRow).get("TableColNum");//获取列数
            if(colNum > 6){
                mapperSheetList.add(multiMapper(sheet,firstRow,colNum));
            }else if(colNum == 6){
                mapperSheetList.add(singleMapper(sheet,firstRow));
            }else{
                return null;
            }
        }
        excelFile.close();
        return mapperSheetList;
    }
    private static MapperSheet multiMapper(Sheet sheet,Row firstRow,int tableColnum){
        // 读取表名 构建旧表和新表
        List<MapperTable> oldTableList = new ArrayList<>();
        MapperTable newTable = new MapperTable(firstRow.getCell(tableColnum-3).getStringCellValue());
        for (int j = 0; j < tableColnum - 3; j = j+3) {
            oldTableList.add(new MapperTable(firstRow.getCell(j).getStringCellValue()));
        }
        // 遍历Excel读取封装表字段信息
        Iterator<Row> rowIterator = sheet.iterator();
        int currentRow = 0;
        Integer lineNum = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (currentRow < 3) {//跳过前三行,
                currentRow++;
                continue;
            } else if ("end".equals(row.getCell(0).getStringCellValue())) {//结束读取
                break;
            }
            //添加域,列遍历
            for (int j = 0,n=0; j < tableColnum -3 ; j=j+3,n++) {
                MapperField field = new MapperField("","","");
                if(row.getCell(j) != null) {
                    String fieldType = "";
                    String fieldRemark = "";
                    if (row.getCell(j + 1) != null) {
                        fieldType = row.getCell(j + 1).getStringCellValue();
                    }
                    if (row.getCell(j + 2) != null) {
                        fieldRemark = row.getCell(j + 2).getStringCellValue();
                    }
                    field = new MapperField(row.getCell(j).getStringCellValue(), fieldType, fieldRemark);
                }
                oldTableList.get(n).getTableFields().add(field);
            }
            MapperField newField = new MapperField("","","");
            if(row.getCell(tableColnum-3) != null){
                String fieldType = "";
                String fieldRemark = "";
                if(row.getCell(tableColnum-2) != null){
                    fieldType = row.getCell(tableColnum-2).getStringCellValue();
                }
                if(row.getCell(tableColnum-1) != null){
                    fieldRemark = row.getCell(tableColnum-1).getStringCellValue();
                }
                newField = new MapperField(row.getCell(tableColnum-3).getStringCellValue(),fieldType,fieldRemark);
            }
            //获取特殊处理域
            String enumStr = "";
            String timeFlag = "";
            String specialCondition = "";
            if(row.getCell(tableColnum)!=null){
                enumStr = row.getCell(tableColnum).getStringCellValue();
            }
            if(row.getCell(tableColnum + 1)!=null){
                timeFlag = row.getCell(tableColnum +1).getStringCellValue();
            }
            if(row.getCell(tableColnum + 2)!=null){
                specialCondition = row.getCell(tableColnum +2).getStringCellValue();
            }
            newField.setEnumMapper(Util.enumMapper(enumStr));
            newField.setSpecialCondition(specialCondition);
            if(timeFlag!=null && !"".equals(timeFlag)){//日期时间拼接，放在新表
                newField.setDateTimeFlag(timeFlag);
            }
            newTable.getTableFields().add(newField);
            lineNum ++;
        }
        MapperSheet mapperSheet = new MapperSheet(newTable,oldTableList,lineNum);
        Row whereRow = sheet.getRow(2);
        mapperSheet.setWhereCondition("");//默认where条件为空
        if(whereRow!=null && whereRow.getCell(0)!=null){
            String whereCondition = sheet.getRow(2).getCell(0).getStringCellValue().toLowerCase();
            if(whereCondition.startsWith("where")){
                mapperSheet.setWhereCondition(whereCondition);
            }
        }
        mapperSheet.setMultiTable(true);
        return mapperSheet;
    }
    private static MapperSheet singleMapper(Sheet sheet,Row firstRow){
        // 读取构建旧表和新表
        MapperTable oldTable = new MapperTable(firstRow.getCell(0).getStringCellValue());
        MapperTable newTable = new MapperTable(firstRow.getCell(3).getStringCellValue());
        // 遍历Excel读取封装表字段信息
        Iterator<Row> rowIterator = sheet.iterator();
        int currentRow = 0;
        Integer lineNum = 0;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if(currentRow<3){//跳过前三行,
                currentRow++;
                continue;
            }else if ("end".equals(row.getCell(0).getStringCellValue())){//结束读取
                break;
            }
            MapperField oldField = new MapperField("","","");
            if(row.getCell(0)!=null){
                String fieldType = "";
                String fieldRemark = "";
                if(row.getCell(1)!=null){
                    fieldType = row.getCell(1).getStringCellValue();
                }
                if(row.getCell(2)!=null){
                    fieldRemark = row.getCell(2).getStringCellValue();
                }
                oldField = new MapperField(row.getCell(0).getStringCellValue(),fieldType,fieldRemark);
            }
            oldTable.getTableFields().add(oldField);
            MapperField newField = new MapperField("","","");
            if(row.getCell(3)!=null){
                String fieldType = "";
                String fieldRemark = "";
                if(row.getCell(4)!=null){
                    fieldType = row.getCell(4).getStringCellValue();
                }
                if(row.getCell(5)!=null){
                    fieldRemark = row.getCell(5).getStringCellValue();
                }
                newField = new MapperField(row.getCell(3).getStringCellValue(),fieldType,fieldRemark);
            }
            //获取特殊处理域
            String enumStr = "";
            String timeFlag = "";
            String specialCondition = "";
            if(row.getCell(6)!=null){
                enumStr = row.getCell(6).getStringCellValue();
            }
            if(row.getCell(7)!=null){
                timeFlag = row.getCell(7).getStringCellValue();
            }
            if(row.getCell(8)!=null){
                specialCondition = row.getCell(8).getStringCellValue();
            }
            newField.setEnumMapper(Util.enumMapper(enumStr));
            newField.setSpecialCondition(specialCondition);
            if(timeFlag!=null && !"".equals(timeFlag)){//日期时间拼接，放在新表
                newField.setDateTimeFlag(timeFlag);
            }
            newTable.getTableFields().add(newField);
            lineNum ++;
        }
        MapperSheet mapperSheet = new MapperSheet(newTable,oldTable);
        Row whereRow = sheet.getRow(2);
        mapperSheet.setWhereCondition("");//默认where条件为空
        if(whereRow!=null && whereRow.getCell(0)!=null){
            if(whereRow.getCell(0)!=null){
                String whereCondition = whereRow.getCell(0).getStringCellValue();
                if(Util.containsIgnoreCase("where",whereCondition) ){
                    mapperSheet.setWhereCondition(whereCondition);
                }
            }
            if(whereRow.getCell(3)!=null) {
                String isEmptyFlag = whereRow.getCell(3).getStringCellValue().toLowerCase();
                if(isEmptyFlag.startsWith("isempty")){
                    mapperSheet.setEmptyFlag(true);
                }
            }
        }
        mapperSheet.setSheetLineNum(lineNum);
        return mapperSheet;
    }

    private static Map<String,Integer> getExcelColNum(Row row) throws Exception {
        int colNum = row.getPhysicalNumberOfCells();//获取列数
        Map<String,Integer> resultMap = new HashMap<>();
        int validNum = 0;
        int tableNum = 0;
        for (int i = 0; i < colNum; i++) {
            String value = row.getCell(i).getStringCellValue();
            if("end".equals(value)) {
                resultMap.put("ValidColNum",validNum);//有效列
                break;
            }
            validNum++;
            if("映射关系".equals(value)){
                resultMap.put("TableColNum",tableNum);//参数表占用列数
                continue;
            }

            tableNum ++;
        }
        resultMap.put("ExcelRealNum",colNum);//文件实际列数
        return resultMap;
    }

    public List<MapperSheet> getMapperSheetList() {
        return mapperSheetList;
    }

    public void setMapperSheetList(List<MapperSheet> mapperSheetList) {
        this.mapperSheetList = mapperSheetList;
    }

    public String getMapperFilePath() {
        return mapperFilePath;
    }

    public void setMapperFilePath(String mapperFilePath) {
        this.mapperFilePath = mapperFilePath;
    }
}
